package com.hugoo.form.sql;

import com.hugoo.core.db.Call;
import com.hugoo.core.db.HSF;
import com.hugoo.core.db.mo.Record;
import com.hugoo.core.db.mo.RecordSet;
import com.hugoo.core.util.TL;
import com.hugoo.err.ConfigError;
import com.hugoo.err.ParaValueEmpty;
import com.hugoo.form.slt.TxtExpAction;
import com.hugoo.form.slt.XlsExpAction;
import com.hugoo.form.slt.XlsxExpAction;
import com.hugoo.form.xo.XFlt;
import com.hugoo.form.xo.XFltPara;
import com.hugoo.form.xo.XHandle;
import com.hugoo.form.xo.XHandle.DBMODL;
import com.hugoo.form.xo.XSql;
import com.hugoo.form.xo.XSql.SqlTpe;
import com.hugoo.rpt.ExpPageInfo;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Lovelock.Chan
 */
public class SqlProc {

    private static Log log = LogFactory.getLog(SqlProc.class);
    private XHandle xhandle;
    private DBMODL dbmodl;

    public SqlProc(XHandle handle) {
        this.xhandle = handle;
        this.dbmodl = xhandle.getModl();
    }

    public Map proc(Map m) {
        if (DBMODL.AUTO == dbmodl) {
            return procAutoModl(m);
        } else if (DBMODL.BATCH == dbmodl) {
            return procBatchModl(m);
        } else if (DBMODL.CUSTOM == dbmodl) {
            return procCustomModl(m);
        }
        return null;
    }

    public ExpPageInfo procByExpModl(Map m, XHandle.RtnYpe ry) {
        //暂时只处理为单SQL语句模式，关于复杂的多SQL语句模式后期再更新
        List<XSql> slist = this.xhandle.getxSqlList();
        if (slist.size() != 1) {
            log.info("【导出数据模式】暂时不支持处理多语句模式！");
            return null;
        }
        XSql xsql = slist.get(0);
        if (XSql.SqlTpe.SQL == xsql.getTpes()) {
            try {
                String ddl = _init_xsql(xsql, m);
                log.info("..exp." + xhandle.getExpColsType() + "..type..." + xhandle.getExpCols());
                //-----------------------------
                XHandle.ExpColsType ect = xhandle.getExpColsType2();
                String expCols = null;
                switch (ect) {
                    case STATIC:
                        expCols = xhandle.getExpCols();
                        break;
                    case AUTO:
                        try {
                            expCols = this.xhandle.getExpCols();
                            expCols = procInst(expCols, xhandle.getInstList(), m, true);
                            expCols = procVar(expCols, xhandle.getAttrList(), m, true);
                            expCols = procVar(expCols, xhandle.getParaList(), m, true);
                        } catch (ParaValueEmpty ex) {
                            log.warn("存在参数值为空的错误配置情况！", ex);
                        }
                        break;
                    case MEM:
                        expCols = (String) m.get(xhandle.getExpCols());
                        break;
                }
                if (TL.isEmpty(expCols)) {
                    log.error("配置导出模板错误，系统终止导出！");
                }
                log.info("导出模板配置内容：" + expCols);
                //-----------------------------
                if (ry == XHandle.RtnYpe.TXT) {
                    if (TL.isEmpty(xhandle.getExpFmt())) { //Txt文本格式导出
                        return new TxtExpAction(xsql.getDbl(), ddl, xhandle.getFileName(), expCols);
                    } else {
                        boolean haveTitle = true, haveEnd = true;
                        String ef = xhandle.getExpFmt();
                        if (ef.matches("^[yYnN][tT][yYnN][eE]$")) {
                            if ("n".equalsIgnoreCase(ef.substring(0, 1))) {
                                haveTitle = false;
                            }
                            if ("n".equalsIgnoreCase(ef.substring(2, 3))) {
                                haveEnd = false;
                            }
                        }
                        return new TxtExpAction(xsql.getDbl(), ddl, xhandle.getFileName(), expCols, haveTitle, haveEnd);
                    }
                } else if (ry == XHandle.RtnYpe.XLS) {
                    //Excel 2003格式导出
                    return new XlsExpAction(xsql.getDbl(), ddl, xhandle.getFileName(), expCols);
                } else if (ry == XHandle.RtnYpe.XLSX) {
                    //Excel 2007格式导出
                    return new XlsxExpAction(xsql.getDbl(), ddl, xhandle.getFileName(), expCols);
                }
            } catch (ConfigError ex) {
                log.info("【导出数据模式】SQL语句部分配置错误！");
            }
        } else {
            log.info("【导出数据模式】暂不支持非SQL语句模式！");
        }
        return null;
    }

    public String crtSql(Map m) {
        List<XSql> slist = this.xhandle.getxSqlList();
        if (slist.size() != 1) {
            log.warn("SQL配置列表不唯一,现不返回数据SQL语句。");
        } else {
            try {
                String sql = _init_xsql(slist.get(0), m);
                return sql;
            } catch (ConfigError ex) {
                log.warn("配置文件错误,现不返回数据SQL语句。");
            }
        }
        return null;
    }

    private Map procAutoModl(Map m) {
        List<XSql> slist = this.xhandle.getxSqlList();
        for (XSql xsql : slist) {
            if (xsql.getTpes() == SqlTpe.PROC) {
                try {
                    //若为存储过程，需要特别处理
                    init_proc(xsql, m);
                } catch (ConfigError ex) {
                    log.info("执行存储过程错误", ex);
                }
            } else {
                try {
                    String ddl = _init_xsql(xsql, m);
                    String vl = xsql.getVar();//存储变量
                    if (xsql.getTpes() == SqlTpe.DDL) {
                        int[] rs = HSF.excute(xsql.getDbl(), ddl);
                        if (TL.isNotEmpty(vl) && rs.length > 0) {//如果变更 不为空，表示需要存储返回值
                            m.put(vl, rs[0] > -1);//返回值是影响行数，若等于-1，表示执行错误
                        } else {
                            log.debug("执行完成，不需要存储返回值！");
                        }
                    } else if (xsql.getTpes() == SqlTpe.SQL) {
                        if (TL.isEmpty(vl)) {
                            log.debug("本查询语句未定义存储变量，现取消执行该语句。");
                        } else {
                            RecordSet<Record> rs = HSF.query(xsql.getDbl(), ddl);
                            m.put(vl, rs);
                        }
                    }
                } catch (Exception e) {
                    log.warn("处理执行语句时出错！", e);
                }
            }
        }
        return m;
    }

    private Map procBatchModl(Map m) {
        List<XSql> slist = this.xhandle.getxSqlList();
        List<String> sqls = new ArrayList();
        for (XSql xsql : slist) {
            if (xsql.getTpes() == SqlTpe.DDL) {
                try {
                    String ddl = _init_xsql(xsql, m);
                    sqls.add(ddl);
                } catch (ConfigError ex) {
                    log.info("配置语句存在错误，本处会跳过这部分配置！", ex);
                }
            }
        }
        try {
            HSF.excute(xhandle.getDbl(), sqls);//批处理语句暂时无返回值
        } catch (Exception ex) {
            log.info("批处理语句存在错误！", ex);
        }
        return m;
    }

    private Map procCustomModl(Map m) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    /**
     * 初始化执行语句
     *
     * @param xsql
     * @return
     */
    private String _init_xsql(XSql xsql, Map m) throws ConfigError {
        if (xsql == null) {
            throw new ConfigError("XSql节点配置错误！");
        }
        String tpl = xsql.getTpl();
        if (TL.isEmpty(tpl)) {
            throw new ConfigError("无效的SQL语句模板！");
        }
        //针对本配置文件，是否需要启用大小写敏感？
        try {
            tpl = procInst(tpl, xsql.getInstList(), m, true);
            tpl = procVar(tpl, xsql.getAttrList(), m, true);
            tpl = procVar(tpl, xsql.getParaList(), m, true);
        } catch (ParaValueEmpty ex) {
            log.warn("存在参数值为空的错误配置情况！", ex);
        }
        List<XFlt> xfl = xsql.getxFltList();
        for (XFlt kv : xfl) {
            tpl = tpl.replaceAll("@" + kv.getId(), this._init_flt(kv, m));
        }
        return tpl;
    }

    private String procVar(String tpl, List<String> instList, Map m, boolean flg) throws ParaValueEmpty {
        if (instList.isEmpty()) {
            return tpl;
        }
        Object obj;
        for (String vl : instList) {
            obj = m.get(vl);
            if (obj != null && obj.toString().trim().length() > 0 || flg) {//有效处理空值
                tpl = tpl.replaceAll(":" + vl, parseVal(obj));//若采用实例方式
            } else {
                throw new ParaValueEmpty();
            }
        }
        return tpl;
    }

    private String parseVal(Object obj) {
        String vl = "";
        if (obj == null) {//若值为空时，返回值也为空
            return vl;
        } else if (obj instanceof String) {
            //若实例
            vl = (String) obj;
        }
        vl = vl.replace("'", "''");
        return vl;
    }

    private String procInst(String tpl, List<String> instList, Map m, boolean flg) throws ParaValueEmpty {
        if (instList.isEmpty()) {
            return tpl;
        }
        for (String inst : instList) {
            Object obj = m.get(inst);
            if (obj == null) {
                if (flg) {
                    //tpl = TL.replace(tpl, "\\$\\{" + inst + "(\\[[0-9]+\\])?(\\.\\w+)?\\}", "");
                    tpl = tpl.replaceAll("\\$\\{" + inst + "(\\[[0-9]+\\])?(\\.\\w+)?\\}", "");
                } else {
                    throw new ParaValueEmpty();
                }
            } else {
                if (obj instanceof RecordSet) {
//                    log.info("模板【" + tpl + "】需要处理的实例值：" + inst);
                    RecordSet rs = (RecordSet) obj;
                    int i = 0;
                    if (tpl.matches("\\$\\{" + inst + "\\}")) {
                        // tpl = TL.replace(tpl, "\\$\\{" + inst + "\\}", rs.toJson());
                        tpl = tpl.replaceAll("\\$\\{" + inst + "\\}", rs.toJson());
//                        log.info("处理后的值："+tpl);
                    }
                    if (rs.first()) {//若存在第一条记录，，，
                        do {
                            Record rcd = rs.getRecord();
                            if (tpl.matches("\\$\\{" + inst + "(\\[[0-9]+\\])?\\}")) {
                                //tpl = TL.replace(tpl, ".*\\$\\{" + inst + "(\\[" + i + "\\])?\\}.*", rcd.toString());
                                tpl = tpl.replaceAll(".*\\$\\{" + inst + "(\\[" + i + "\\])?\\}.*", rcd.toString());
                            }
                            if (tpl.matches(".*\\$\\{" + inst + "(\\[[" + i + "]+\\])?(\\.\\w+)?\\}.*")) {
                                Set<String> sx = rcd.keySet();
                                for (String s : sx) {
                                    String vl = rcd.getString(s);
//                                log.info("处理键..." + s + ":" + vl);
                                    if (i == 0) {
                                        //   tpl = TL.replace(tpl, "\\$\\{" + inst + "\\." + s + "\\}", parseVal(vl));
                                        tpl = tpl.replaceAll("\\$\\{" + inst + "\\." + s + "\\}", parseVal(vl));
                                    }
                                    // tpl = TL.replace(tpl, "\\$\\{" + inst + "(\\[" + i + "\\]){1}\\." + s + "\\}", parseVal(vl));
                                    tpl = tpl.replaceAll("\\$\\{" + inst + "(\\[" + i + "\\]){1}\\." + s + "\\}", parseVal(vl));
                                }
                            }
                            i++;
                        } while (rs.next());
                    }
                    //清理不存在的数据值？？  --本处暂时不用清理
                }
            }
        }
        return tpl;
    }

    public static void main(String[] args) {
        String xx = "xxc ${tstx[23].vxl} xxd ${tstx[23].vxxc} dsd";
        System.out.println(xx.matches("^call \\w+\\(.*\\)$"));
    }

    /**
     * 初始化存储过程
     *
     * @param xsql
     * @param m
     */
    private void init_proc(XSql xsql, final Map m) throws ConfigError {
        String ddl = _init_xsql(xsql, m);
        final String tpl = ddl;
        if (tpl != null && tpl.matches("^CALL \\w+\\(.*\\)$")) {
            String para = tpl.substring(tpl.indexOf("(") + 1, tpl.lastIndexOf(")"));
            final String pc, px[];
            if (para.trim().length() > 0) {
                px = para.split(",");
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < px.length; i++) {
                    sb.append(",?");
                }
                if (px.length > 0) {
                    pc = sb.substring(1);
                } else {
                    pc = "";
                }
            } else {
                pc = "";
                px = new String[]{};
            }
            HSF.call(xsql.getDbl(), new Call() {
                @Override
                public void proc(CallableStatement cs, Log log) throws SQLException {
                    int i = 0;
                    List<String> ls = new ArrayList();
                    for (String p : px) {
                        i++;
                        if (p.startsWith(":")) {
                            Object mx = m.get(p.substring(1));
                            if (mx != null && mx instanceof String) {
                                cs.setString(i, (String) mx);
                            } else {
                                log.info("参数值【" + p + "】无效，现以缺省值传入。");
                            }
                        } else if (p.startsWith("@")) {
                            cs.registerOutParameter(i, Types.VARCHAR);
                            ls.add(i + p.substring(1));
                        } else {
                            cs.setString(i, p);
                        }
                    }
                    cs.execute();
                    for (String s : ls) {
                        String v = s.substring(0, 1);
                        m.put(s.substring(1), cs.getString(TL.parseInt(v)));
                    }
                }

                @Override
                public String procSql() {
                    return tpl.substring(0, tpl.indexOf("(") + 1) + pc + ")";
                }
            });
        } else {
            log.info("无效的存储过程调用【" + tpl + "】");
        }
    }

    private String _init_flt(XFlt xf, Map m) {
        String tpl = xf.getTpl();
        if (TL.isEmpty(tpl)) {
            log.warn("参考模板【" + xf.getId() + "】存在无效配置模板");
            return "";
        }
        try {
            tpl = procInst(tpl, xf.getInstList(), m, true);
            tpl = procVar(tpl, xf.getAttrList(), m, true);
            tpl = procVar(tpl, xf.getParaList(), m, true);
        } catch (ParaValueEmpty ex) {
        }
        List<XFltPara> fplt = xf.getxFltParaList();
        for (XFltPara xfp : fplt) {
            tpl = tpl.replaceAll("@" + xfp.getId(), this._init_xfp(xfp, m));
        }
        return tpl;
    }

    private String _init_xfp(XFltPara xfp, Map m) {
        String tpl = xfp.getSql();
        try {
            tpl = procVar(tpl, xfp.getAttrList(), m, false);
            tpl = procVar(tpl, xfp.getParaList(), m, false);
            tpl = procInst(tpl, xfp.getInstList(), m, false);
            return tpl;
        } catch (ParaValueEmpty ex) {
            //或存在异常时，根据预设置值来替代
            String nrp = xfp.getNrp();
            if (TL.isEmpty(nrp)) {
                nrp = xfp.getxFlt().getNrp();
                if (TL.isEmpty(nrp)) {
                    nrp = "";
                }
            }
            return nrp;
        }
    }

    public String prsRtnData(Map m) {
        String tpl = this.xhandle.getRtn();
        if (this.xhandle.getRtnYpe() == XHandle.RtnYpe.CST) {
            tpl = tpl.toUpperCase();
        }
        try {
            tpl = procVar(tpl, xhandle.getAttrList(), m, false);
            tpl = procVar(tpl, xhandle.getParaList(), m, false);
            tpl = procInst(tpl, xhandle.getInstList(), m, false);
        } catch (ParaValueEmpty ex) {
            log.warn("参数配置存在为空的错误配置");
        }
        return tpl;
    }

    public class KV {

        String k, v;
    }
}
